﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using University.Workshop.Model;
using System.Data;
using System.Globalization;
using System.Diagnostics;

namespace University.Workshop.DataAccess
{
    public static class GroupDataAccess
    {
        public const string ConnString = "Data Source=.\\SQLEXPRESS;Initial Catalog=MedicalDatabase;Integrated Security = True";

        public static Group InsertGroup(Group group)
        {
            if (group != null)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [Group](Company,Discount,Status) VALUES(@Company,@Discount,@Status)";
                    cmd.Parameters.AddWithValue("Company", group.Company);
                    cmd.Parameters.AddWithValue("Discount", group.Discount);
                    cmd.Parameters.AddWithValue("Status", 1);

                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error inserting a group in the database");
                        }
                        return group;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error inserting a group in the database SQL Exception" + ex);
                    }
                }
            }
            else
                return null;
        }

        public static Group GetGroupByCompany(string name)
        {
            if (!string.IsNullOrEmpty(name))
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [Group] Where Company = @Name";
                    cmd.Parameters.AddWithValue("Name", name);
                    conn.Open();
                    try
                    {

                        SqlDataReader reader = cmd.ExecuteReader();
                        if (!reader.HasRows)
                        {
                            return null;
                        }
                        Group group = new Group();
                        while (reader.Read())
                        {
                            group.Company = reader["Company"].ToString();
                        }
                        return group;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error obteniendo el grupo", ex);
                    }
                }
            }
            else return null;
        }

        public static DataTable ShowGroups()
        {
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                SqlCommand cmd = conn.CreateCommand();
                DataTable table = null;
                DataTable tableAux = null;
                cmd.CommandText = "SELECT Company,Discount FROM [Group] WHERE Status='1'";
                conn.Open();
                try
                {

                    SqlDataReader reader = cmd.ExecuteReader();
                    table = new DataTable();
                    table.Locale = CultureInfo.InvariantCulture;

                    table.Load(reader);
                    tableAux = table;

                    return tableAux;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error obteniendo los grupos", ex);
                }

                finally
                {
                    if (table != null)
                    {
                        table.Dispose();
                    }
                }
            }
        }

        public static bool UpdateGroup(Group group)
        {
            if (group != null)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE [Group] SET [Discount] = @Discount WHERE [Company] = @Company";
                    cmd.Parameters.AddWithValue("Company", group.Company);
                    cmd.Parameters.AddWithValue("Discount", group.Discount);

                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error updating a group in the database");
                        }
                        return true;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error updating a group in the database SQL Exception" + ex);
                    }
                }
            }
            else
                return false;

        }

        public static bool DeleteGroup(string value)
        {
            if (!String.IsNullOrEmpty(value))
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE [Group] SET [Status] = 0 WHERE [Company] = @Company";
                    cmd.Parameters.AddWithValue("Company", value);

                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error deleting a group in the database");
                        }
                        return true;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error deleting a group in the database SQL Exception" + ex);
                    }

                }
            }
            else
                return false;
        }
    }
}
